-- @owner: zou_jialiang050
-- @date: 2024-15-06
-- @testpoint: merge into using，部分测试点合理报错

--step1:建表;expect:成功
drop table if exists t_ustore_regr_syy_024;
create table  t_ustore_regr_syy_024(
     col_1 integer,
     col_2 bigint,
     col_3 float8,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);
--创建序列
drop sequence if exists seq_ustore_regr_syy_024 cascade;
create sequence seq_ustore_regr_syy_024 increment by 1 start with 10;

--清空数据
truncate table t_ustore_regr_syy_024;

--插入数据
begin
	for i in 1..50 loop
      insert into t_ustore_regr_syy_024 values(
	  i,
      seq_ustore_regr_syy_024.nextval+i,
	  i+445.255,
	  98*0.99*i,
	  true,
	  lpad('abc','30','@')::varchar,
	  lpad('abc','50','b')::varchar2,
	  lpad('abc','30','@')::varchar,
	  (interval '4 5:12:10.222' day to second(3)),
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  lpad('10','12','01010')::blob,
	  rpad('abc','9','a@123&^%djgk')::clob,
	  '{32,535,5645645,6767,76,67,56,48,979,978,7}'
	  );
    end loop;
end;
/
insert into t_ustore_regr_syy_024 select * from t_ustore_regr_syy_024;
insert into t_ustore_regr_syy_024 select * from t_ustore_regr_syy_024;

--step2:merge into using;expect:部分测试点合理报错
drop table if exists t_ustore_regr_syy_024_1;
create table t_ustore_regr_syy_024_1 with (storage_type=ustore) as select distinct col_1,col_2,col_3,col_4,col_5 from t_ustore_regr_syy_024;
merge into t_ustore_regr_syy_024 t1 using t_ustore_regr_syy_024_1 t2 on (t1.col_2=t2.col_2)
when matched then update set t1.col_1=regr_syy(t2.col_1,t2.col_1)
when not matched then insert(t1.col_2) values(2000);
merge into t_ustore_regr_syy_024 t1 using t_ustore_regr_syy_024_1 t2 on (t1.col_2!=t2.col_2)
when matched then update set t1.col_1=1000
when not matched then insert(t1.col_2) values(regr_syy(t2.col_1,t2.col_1));

--step3:清理环境;expect:成功
drop sequence if exists seq_ustore_regr_syy_024 cascade;
drop table if exists t_ustore_regr_syy_024_1 cascade;
drop table if exists t_ustore_regr_syy_024 cascade;